# -*- coding: utf-8 -*-
# 低电量告警未推送问题
# 对比 alert 表与 gateway 表记录是否一一对应
# author yeyi
# date 2021-04-06

import xlwt
import xlrd
import pymongo
import csv
import bson
import time

# 告警日期与下发充电日期差多少秒以内表示匹配
dateGap = 3600
# 匹配时车控时间可以比上传告警时间早多少秒
offset = 60
# 低电压充电功能在这个时间后生效 2020-09-02(毫秒)，即只对比这个时间戳之后的记录
minDate = 1598976000000

# alert mongo 数据库地址
conn = pymongo.MongoClient("mongodb://mongouser:tce%40guangqi2019@10.123.16.115:27017/vehicle?authSource=admin")
#conn = pymongo.MongoClient("mongodb://mongouser:pretce%40guangqi@10.123.2.13:27017/alert?authSource=admin")
db = conn['alert']
alert = db.alert

# iov-gateway 项目数据库地址
conn2 = pymongo.MongoClient("mongodb://mongouser:tce%40guangqi2019@10.123.18.20:27017/vehicle?authSource=admin")
#conn2 = pymongo.MongoClient("mongodb://mongouser:pretce%40guangqi@10.123.2.13:27017/iov_vehicle_gateway?authSource=admin")
db2 = conn2['iov_vehicle_gateway']
vehicle_access_record = db2.vehicle_access_record

rxls = xlwt.Workbook()  # 新建工作簿
rxls.add_sheet('Sheet1', cell_overwrite_ok=True)
rxls.add_sheet('Sheet')

# 结果输出文件
f1 = open('v1.csv', 'w', encoding='utf-8')
csv_writer_1 = csv.writer(f1)
csv_writer_1.writerow(['vin', 'gcCode', '告警日期', '告警内容','告警原始内容','下发充电日期','下发充电返回'])

# 要查询的 vin 列表
data = xlrd.open_workbook('1.xls')
# data = xlrd.open_workbook('2.xls')
sheet1 = data.sheet_by_name('高配')

alerts = []
gateways = []

# 名词解释：
# left 开头变量代表 alert 表的相关数据
# right 开头变量代表 gateway 表的相关数据

for i in range(1, sheet1.nrows):
    vin = sheet1.cell(i, 0).value
    result = alert.find({
        "uploadTime": { "$gt":minDate },
        "alarmSource": 203,
        "alarmObject": bson.Int64("1"),
        "alarmReason": bson.Int64("1"),
        "vin": vin
    }).sort([("uploadTime",-1)])

    leftList = list(result)
    result2 = vehicle_access_record.find({
        "beginTime": { "$gt":minDate },
        "request.identifier.event":"control_highlow_charging",
        "vin": vin
    }).sort([("time",-1)])
    rightList = list(result2)

    iLeft=0
    iRitht=0
    # 0左右都增加了 1左增加了 -1右增加了
    leftRight = 0
    leftMax = len(leftList)
    rightMax = len(rightList)
    while iLeft < leftMax or iRitht<rightMax:
        if iLeft>=leftMax and iRitht>=rightMax:
          if (0==leftMax and 0!=rightMax) or (0!=leftMax and 0==rightMax):
            pass # 有一个无数据另外一个有也要执行
          else:
            break
          
        item = None
        leftTime = None
        timeArray = None
        leftTimeStr = None
        if iLeft<leftMax:
          item = leftList[iLeft]
          leftTime = item['uploadTime']/1000
          timeArray = time.localtime(leftTime)
          leftTimeStr = time.strftime("%Y-%m-%d %H:%M:%S", timeArray)

        global rightResponse
        rightResponse = None
        righttime = None
        rightTimeStr = None
        global msg
        msg = ''
        if iRitht<rightMax:
          gateTime = rightList[iRitht]['time']
          righttime = time.mktime(time.strptime(gateTime.strftime('%Y-%m-%d %H:%M:%S'), '%Y-%m-%d %H:%M:%S'))
          righttime = 8*60*60 + righttime
          timeArray = time.localtime(righttime)
          rightTimeStr = time.strftime("%Y-%m-%d %H:%M:%S", timeArray)
          if 'response' in rightList[iRitht]:
            rightResponse = rightList[iRitht]['response']
            if rightResponse is not None:
              if 'msg' in rightResponse:
                msg = rightResponse['msg']
              else:
                msg = '无结果'
          else:
            msg = '无结果2'
        
        global line
        line = []

        # 如果左表用完插入右表
        if iLeft>=leftMax:
          line = [vin, '', '', '','', rightTimeStr,msg]
          iRitht = iRitht+1
          leftRight = -1
        # 右表用完插左表
        elif iRitht>=rightMax:
          line = [vin, item['gcCode'], leftTimeStr, item['alarmMessage'],item['receiveCommand'], '','']
          iLeft = iLeft+1
          leftRight = 1
        # 如果右表时间更小,表明左表肯定匹配不上了，先把匹配不上的左表插入
        elif righttime+offset < leftTime:
            line = [vin, item['gcCode'], leftTimeStr, item['alarmMessage'],item['receiveCommand'], '','']
            iLeft = iLeft+1
            leftRight = 1
        # 左右时间相差1小时内合并
        elif righttime+offset >=leftTime and righttime+offset<leftTime+dateGap:
          gcCode = item['gcCode']
          alarmTime = leftTimeStr
          alarmMsg = item['alarmMessage']
          alarmCmd = item['receiveCommand']
          iLeft = iLeft+1
          pushMsg = msg
          pushTime = rightTimeStr
          iRitht = iRitht+1
          leftRight = 0
          line = [vin, gcCode, alarmTime, alarmMsg,alarmCmd, pushTime,pushMsg]
        # 左表时间小，只插右表
        else:
          line = [vin, '', '', '','', rightTimeStr,msg]
          iRitht = iRitht+1
          leftRight = -1

        csv_writer_1.writerow(line)

f1.close()

